﻿
using B.S.InOut.Domain;
using B.S.InOutErrorCode;
using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace B.S.InOut.Data.OutManage
{
    public class OutManageRepository : IOutManageRepository
    {
        // 数据库连接字符串
        private readonly string _connectionString;

        // 构造函数，接收数据库连接字符串
        public OutManageRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public async Task<int> DeleteOutMenageAsync(int id)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义删除 SQL 语句
                var sql = "DELETE FROM Outmanagement WHERE Id = @id";
                // 执行删除操作并返回受影响的行数
                return await connection.ExecuteAsync(sql, new { Id = id });
            }
        }



        public async Task<int> DeleteOutMenageAsyncd(IEnumerable<int> productIds)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 构建删除 SQL 语句，使用 IN 子句来指定要删除的产品 ID
                var sql = "DELETE FROM Outmanagement WHERE Id IN @ProductIds";
                // 使用 Dapper 的 DynamicParameters 来传递参数
                var parameters = new DynamicParameters();
                parameters.Add("@ProductIds", productIds);

                // 执行删除操作并返回受影响的行数
                return await connection.ExecuteAsync(sql, parameters);
            }
        }

        public async Task<int> GetOutMenageAsyncdIds(IEnumerable<int> productIds)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 构建删除 SQL 语句，使用 IN 子句来指定要删除的产品 ID
                var sql = "select * FROM Outmanagement WHERE Id IN @ProductIds";
                // 使用 Dapper 的 DynamicParameters 来传递参数
                var parameters = new DynamicParameters();
                parameters.Add("@ProductIds", productIds);

                // 执行删除操作并返回受影响的行数
                return await connection.ExecuteAsync(sql, parameters);
            }
        }




        public async Task<IEnumerable<Outmanagement>> GetAllOutMProAsync()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义查询所有产品的 SQL 语句
                var sql = " select* from outManagement a join ProductModel b on a.Pid=b.ProductId where 1=1";
                // 执行查询操作并返回所有产品
                return await connection.QueryAsync<Outmanagement>(sql);
            }
        }


        public async Task<IEnumerable<Outmanagement>> GetAllOutMenageAsync()
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义查询所有产品的 SQL 语句
                var sql = "SELECT * FROM Outmanagement";
                // 执行查询操作并返回所有产品
                return await connection.QueryAsync<Outmanagement>(sql);
            }
        }


        public async Task<IEnumerable<Outmanagement>> GetOutMenageByConditionsAsync(string? Outboundnumber, int? Outbound, int? OutAuditstatus, int pageindex, int pageSize)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 构建基础的 SQL 查询语句
                var sql = "select * from  Outmanagement where 1=1";
                var parameters = new DynamicParameters();

                // 如果产品名称不为空，添加名称查询条件
                if (!string.IsNullOrEmpty(Outboundnumber))
                {
                    sql += " AND Outboundnumber like @Outboundnumber";
                    parameters.Add("@Outboundnumber", $"%{Outboundnumber}%");
                }

                // 如果最低价格不为空，添加最低价格查询条件
                if (Outbound!=null)
                {
                    sql += " AND Outbound = @Outbound";
                    parameters.Add("@Outbound", Outbound);
                }

                // 如果最高价格不为空，添加最高价格查询条件
                if (OutAuditstatus != null)
                {
                    sql += " AND OutAuditstatus = @OutAuditstatus";
                    parameters.Add("@OutAuditstatus", $"{OutAuditstatus}");
                }
                // 添加分页逻辑
                var offset = (pageindex - 1) * pageSize;
                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", pageSize);
                parameters.Add("@pageindex", pageindex);



                // 执行查询并返回结果
                return await connection.QueryAsync<Outmanagement>(sql, parameters);
            }
        }



        /// <summary>
        /// 根据 ID 获取单条数据的异步实现
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Outmanagement> GetOutMenageByIdAsync(int id)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义查询 SQL 语句
                var sql = "SELECT * FROM Outmanagement WHERE Id = @id";
                // 执行查询操作并返回单个产品
                return await connection.QueryFirstOrDefaultAsync<Outmanagement>(sql, new { Id = id });
            }
        }

        static List<string> SplitData(string data)
        {
            data = data.Trim('{', '}');
            return data.Split(new[] { "},{", "}" }, StringSplitOptions.RemoveEmptyEntries).ToList();
        }
        /// <summary>
        /// 插入出库信息到数据库的异步实现
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public async Task<int> InsertOutMenageAsync(Outmanagement product)
        {
            // 拆分相关数据
            var batches = SplitData(product.batch);
            var specifications = SplitData(product.Specification);
            var quantities = SplitData(product.Quantity);
            var totalPrices = SplitData(product.Totalprice);
            var productIds = SplitData(product.ProductId);
            var productNames = SplitData(product.ProductName);
            var prices = SplitData(product.Price);
            var binlocations = SplitData(product.Binlocation);

            // 用于存储最后插入记录的 ID
            int lastInsertedId = 0;

            // 使用 using 语句确保数据库连接在使用后正确关闭和释放资源
            using (var connection = new SqlConnection(_connectionString))
            {
                try
                {
                    // 异步打开数据库连接
                    await connection.OpenAsync();

                    for (int i = 0; i < productNames.Count; i++)
                    {
                        // 定义插入 SQL 语句
                        var sql = "INSERT INTO Outmanagement (Pid,Outboundnumber,AssociatedNum,batch,Specifications,Quantity,Totalprice,Supplier,SupplierID,SupplierName,SupplierPeople,SupplierPhone,Location,Outbound,Customer,OutCreator,OutReviewer,OutAuditTime,OutAuditstatus,OutCreationTime,OutModifiedBy,OutChangeTime,Rember,IsDelete,ShenRember,ProductId,ProductNum,ProductName,Specification,Price,Binlocation) VALUES (@Pid,@Outboundnumber,@AssociatedNum,@batch,@Specifications,@Quantity,@Totalprice,@Supplier,@SupplierID,@SupplierName,@SupplierPeople,@SupplierPhone,@Location,@Outbound,@Customer,@OutCreator,@OutReviewer,@OutAuditTime,@OutAuditstatus,@OutCreationTime,@OutModifiedBy,@OutChangeTime,@Rember,@IsDelete,@ShenRember,@ProductId,@ProductNum,@ProductName,@Specification,@Price,@Binlocation); SELECT CAST(SCOPE_IDENTITY() as int);";

                        // 执行插入操作并返回插入记录的 ID
                        var parameters = new
                        {
                            Pid = product.PId,
                            Outboundnumber = product.Outboundnumber,
                            AssociatedNum = product.AssociatedNum,
                            batch = batches[i],
                            Specifications = specifications[i],
                            Quantity = int.Parse(quantities[i]),
                            Totalprice = decimal.Parse(totalPrices[i]),
                            Supplier = product.Supplier,
                            SupplierID = product.SupplierID,
                            SupplierName = product.SupplierName,
                            SupplierPeople = product.SupplierPeople,
                            SupplierPhone = product.SupplierPhone,
                            Location = product.Location, // 这里应根据实际逻辑确定，假设默认值
                            Outbound = product.Outbound,
                            Customer = product.Customer, // 这里应根据实际逻辑确定，假设默认值
                            OutCreator = product.OutCreator,
                            OutReviewer = product.OutReviewer,
                            OutAuditTime = product.OutAuditTime,
                            OutAuditstatus = product.OutAuditstatus,
                            OutCreationTime = product.OutCreationTime,
                            OutModifiedBy = product.OutModifiedBy, // 这里应根据实际逻辑确定，假设默认值
                            OutChangeTime = DateTime.Now, // 这里应根据实际逻辑确定，假设当前时间
                            Rember = product.Rember,
                            IsDelete = product.IsDelete,
                            ShenRember = product.ShenRember,
                            ProductId = productIds[i],
                            ProductNum = int.Parse(quantities[i]),
                            ProductName = productNames[i],
                            Specification = specifications[i],
                            Price = decimal.Parse(prices[i]),
                            Binlocation = binlocations[i]
                        };

                        // 执行插入操作并获取插入记录的 ID
                        lastInsertedId = await connection.ExecuteScalarAsync<int>(sql, parameters);
                    }
                }
                catch (Exception ex)
                {
                    // 处理异常，可根据实际需求进行日志记录或其他操作
                    Console.WriteLine($"插入数据时发生错误: {ex.Message}");
                    // 发生异常时返回 -1 表示插入失败
                    return -1;
                }
            }

            return lastInsertedId;
        }

        public async Task<int> UpdateOutMenageAsync(Outmanagement product)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 定义更新 SQL 语句
                var sql = "UPDATE Outmanagement SET Pid = @Pid,Outboundnumber = @Outboundnumber,AssociatedNum = @AssociatedNum,batch = @batch,Specifications = @Specifications,Quantity = @Quantity,Totalprice = @Totalprice,Supplier = @Supplier,SupplierID = @SupplierID,SupplierName = @SupplierName,SupplierPeople = @SupplierPeople,SupplierPhone = @SupplierPhone,Location=@Location,Outbound=@Outbound,Customer=@Customer,OutCreator=@OutCreator,OutReviewer=@OutReviewer,OutAuditTime=@OutAuditTime,OutAuditstatus=@OutAuditstatus,OutCreationTime=@OutCreationTime,OutModifiedBy=@OutModifiedBy,OutChangeTime=@OutChangeTime,Rember=@Rember,IsDelete=@IsDelete,ShenRember=@ShenRember,ProductId=@ProductId,ProductNum=@ProductNum,ProductName=@ProductName,Specification=@Specification,Price=@Price,Binlocation=@Binlocation WHERE Id = @Id";
                // 执行更新操作并返回受影响的行数
                return await connection.ExecuteAsync(sql, product);
            }
        }

        public async Task<IEnumerable<OutmanagementProduct>> GetOutmanagementWithProductModelAsync(string? Outboundnumber, ApiOutbound? Outbound, ApiStatus? OutAuditstatus, int pageindex, int pageSize)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 编写联合查询的 SQL 语句，通过 PId 和 ProductId 关联两个表
                var sql = @"
                    select * from Outmanagement where 1=1
                ";
                //                om.*,
                //                        pm.ProductId, om
                //                        pm.ProductName,
                //                        pm.ManufacturerCode,
                //                        pm.InternalCode,
                //                        pm.UnitId,
                //                        pm.Category,
                //                        pm.UpperLimit,
                //                        pm.LowerLimit,
                //                        pm.Specification,
                //                        pm.Price,
                //                        pm.WHouseId,
                //                        pm.WHouseTypeId,
                //                        pm.Description
                //JOIN
                //                        ProductModel pm ON om.PId = CAST(pm.ProductId as NVARCHAR(50))
                var parameters = new DynamicParameters();

                // 如果产品名称不为空，添加名称查询条件
                if (!string.IsNullOrEmpty(Outboundnumber))
                {
                    sql += " AND Outboundnumber = @Outboundnumber";
                    parameters.Add("@Outboundnumber",Outboundnumber);
                }

                // 如果最低价格不为空，添加最低价格查询条件
                if (Outbound != null)
                {
                    sql += " AND Outbound = @Outbound";
                    parameters.Add("@Outbound", Outbound);
                }
                // 如果最低价格不为空，添加最低价格查询条件
                if (OutAuditstatus != null)
                {
                    sql += " AND OutAuditstatus = @OutAuditstatus";
                    parameters.Add("@OutAuditstatus", OutAuditstatus);
                }
                // 添加分页逻辑
                var offset = (pageindex - 1) * pageSize;
                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", pageSize);
                parameters.Add("@pageindex", pageindex);

                // 使用 Dapper 的 QueryAsync 方法执行查询，并返回结果
                return await connection.QueryAsync<OutmanagementProduct>(sql, parameters);
            }
        }

        public async Task<OutmanagementProduct> GetOutmanagementAndProductModelByIdAsync(int Id)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                // 编写根据出库 ID 查询的 SQL 语句，通过 PId 和 ProductId 关联两个表，并筛选出指定 ID 的出库记录
                var sql = @"
                    SELECT 
                        om.*,
                        pm.ProductId,
                        pm.ProductName,
                        pm.ManufacturerCode,
                        pm.InternalCode,
                        pm.UnitId,
                        pm.Category,
                        pm.UpperLimit,
                        pm.LowerLimit,
                        pm.Specification,
                        pm.Price,
                        pm.WHouseId,
                        pm.WHouseTypeId,
                        pm.Description
                    FROM 
                        Outmanagement om
                    JOIN 
                        ProductModel pm ON om.PId = CAST(pm.ProductId as NVARCHAR(50))
                    WHERE 
                        om.Id = @Id
                ";

                // 使用 Dapper 的 QueryFirstOrDefaultAsync 方法执行查询，并返回结果
                return await connection.QueryFirstOrDefaultAsync<OutmanagementProduct>(sql, new { Id = Id });
            }
        }

        /// <summary>
        /// 根据出库单编号查询
        /// </summary>
        /// <param name="outboundNumber"></param>
        /// <returns></returns>
        public async Task<IEnumerable<Outmanagement>> GetOutmanagementByOutboundNumberAsync(string outboundNumber)
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                var sql = @"SELECT  *  FROM Outmanagement  WHERE Outboundnumber = @OutboundNumber";

                return await connection.QueryAsync<Outmanagement>(sql, new { OutboundNumber = outboundNumber });
            }
        }
    }
}
